package org.microblog.dao.impl;

//public class BlogDaoImpl extends BaseDao implements BlogDao {
//    public BlogDaoImpl(Connection conn) {
//        super(conn);
//    }
//    @Override
//    public int getBlogCount(int uId) throws SQLException{
//        ResultSet rs = null;
//        //获取我的微博数
//        String sql = " select count(*) from blog where uid = ?" ;
//        int count = 0;
//
//        try{
//            rs = this.executeQuery(sql, uId);
//            while (rs.next()) {
//                count = rs.getInt("count(*)");
//            }
//        }catch (SQLException e) {
//            e.printStackTrace();
//            throw e;
//        } finally {
//            DatabaseUtil.closeAll(null, null, rs);
//        }
//        return count;
//    }
//
//    @Override
//    public int getForwardCountByBid(int bid) throws SQLException{
//        ResultSet rs = null;
//        //获取微博转发数
//        String sql = " select count(*) from blog where fromBid = ?" ;
//        int count = 0;
//
//        try{
//            rs = this.executeQuery(sql, bid);
//            while (rs.next()) {
//                count = rs.getInt("count(*)");
//            }
//        }catch (SQLException e) {
//            e.printStackTrace();
//            throw e;
//        } finally {
//            DatabaseUtil.closeAll(null, null, rs);
//        }
//        return count;
//    }
//
//    @Override
//    public Blog findBlogById(int id) throws SQLException{
//        Blog blog = null;
//        ResultSet rs = null;
//        String sql = "select b1.id,b1.uid,b1.content,b1.images,b1.time,b1.state, u1.nickname as u_nickname, u1.image as u_image, u1.address as u_address, u1.mysite as u_mysite,b2.id as f_id, b2.content as f_content, b2.images as f_images, u2.nickname as f_nickname from (select * from blog where id=?) b1 left join blog b2  on b1.fromBid = b2.id left join user u1 on b1.uid = u1.id left join user u2 on b2.uid = u2.id ";
//        try{
//            rs = this.executeQuery(sql,id);
//            while (rs.next()) {
//                blog = new Blog();
//                blog.setId(rs.getInt("id"));
//                blog.setuId(rs.getInt("uid"));
//                blog.setContent(rs.getString("content"));
//                blog.setImages(rs.getString("images"));
//                blog.setTime(rs.getTimestamp("time"));
//                blog.setState(rs.getInt("state"));
//                blog.getUser().setNickname(rs.getString("u_nickname"));
//                blog.getUser().setImage(rs.getString("u_image"));
//                blog.getUser().setAddress(rs.getString("u_address"));
//                blog.getUser().setMysite(rs.getString("u_mysite"));
//                if(blog.getState()==2){
//                    Blog fBlog = new Blog();
//                    //检查转发的原始微博是否被删除
//                    if(rs.getObject("f_id")!=null){
//                        fBlog.setId(rs.getInt("f_id"));
//                        fBlog.getUser().setNickname(rs.getString("f_nickname"));
//                        fBlog.setContent(rs.getString("f_content"));
//                        fBlog.setImages(rs.getString("f_images"));
//                        blog.setOrigin(fBlog);
//                    }
//                }
//            }
//        }catch (SQLException e) {
//            e.printStackTrace();
//            throw e;
//        } finally {
//            DatabaseUtil.closeAll(null, null, rs);
//        }
//        return blog;
//    }
//
//    @Override
//    public int deleteBlog(int id) throws SQLException{
//        String sql = "delete from blog where id = ?";
//        int result=0;
//        try{
//            result = this.executeUpdate(sql, id);
//        }catch (Exception e) {
//            e.printStackTrace();
//            throw e;
//        }
//        return result;
//    }
//
//    @Override
//    public int getAllBlogsCount(int uId, String keyword) throws SQLException {
//        ResultSet rs = null;
//        //获取我和我的关注人的微博总数
//        String sql = "select count(*) from blog b1 left join blog b2 on b1.fromBid=b2.id " +
//                "where (b1.uid = ? or b1.uid in (select followedUid from follow where uid = ?)) " +
//                "and (b1.content like ? or b2.content like ? )";
//        int count = 0;
//        try{
//            rs = this.executeQuery(sql, uId, uId, "%"+keyword+"%", "%"+keyword+"%");
//            while (rs.next()) {
//                count = rs.getInt("count(*)");
//            }
//        }catch (SQLException e) {
//            e.printStackTrace();
//            throw e;
//        } finally {
//            DatabaseUtil.closeAll(null, null, rs);
//        }
//        return count;
//    }
//
//    @Override
//    public List<Blog> getAllBlogsByPage(int uId, String keyword, int pageNo, int pageSize) throws SQLException {
//        List<Blog> list = new ArrayList<Blog>();
//        ResultSet rs = null;
//        //获取我和我的关注人的所有微博,并按照降序排列
//        String fuzzyQuery = ""; //定义模糊查询部分字符串
//        if(keyword!=null && !keyword.equals("")){
//            fuzzyQuery = "and (b1.content like ? or b2.content like ?)";
//        }
//        String sql = " select b1.id,b1.uid, b1.content,b1.images,b1.time,b1.state, " +
//                "u1.nickname as u_nickname, u1.image as u_image,b2.id as f_id, " +
//                "b2.content as f_content, b2.images as f_images, u2.nickname as f_nickname " +
//                "from blog b1 left join blog b2 on b1.fromBid = b2.id " +
//                "left join user u1 on b1.uid=u1.id " +
//                "left join user u2 on b2.uid=u2.id where (b1.uid = ? or b1.uid in " +
//                "(select followedUid from follow where uid = ?)) " +
//                fuzzyQuery +
//                "order by b1.time desc LIMIT ?, ?";
//
//        try{
//            if(keyword.equals("")){
//                rs = this.executeQuery(sql,uId, uId,(pageNo - 1) * pageSize,pageSize);
//            }else{
//                rs = this.executeQuery(sql,uId, uId, "%"+keyword+"%", "%"+keyword+"%",
//                        (pageNo - 1) * pageSize,pageSize);
//            }
//            Blog blog = null;
//            while (rs.next()) {
//                blog = new Blog();
//                blog.setId(rs.getInt("id"));
//                blog.setuId(rs.getInt("uid"));
//                blog.getUser().setNickname(rs.getString("u_nickname"));
//                blog.getUser().setImage(rs.getString("u_image"));
//                blog.setContent(rs.getString("content"));
//                blog.setImages(rs.getString("images"));
//                blog.setTime(rs.getTimestamp("time"));
//                blog.setState(rs.getInt("state"));
//                if(blog.getState()==2){
//                    Blog fBlog = new Blog();
//                    //检查转发的原始微博是否被删除
//                    if(rs.getObject("f_id")!=null){
//                        fBlog.setId(rs.getInt("f_id"));
//                        fBlog.getUser().setNickname(rs.getString("f_nickname"));
//                        fBlog.setContent(rs.getString("f_content"));
//                        fBlog.setImages(rs.getString("f_images"));
//                        blog.setOrigin(fBlog);
//                    }
//                }
//                list.add(blog);
//            }
//        }catch (SQLException e) {
//            e.printStackTrace();
//            throw e;
//        } finally {
//            DatabaseUtil.closeAll(null, null, rs);
//        }
//        return list;
//    }
//
//    @Override
//    public int addNewBlog(Blog blog) throws SQLException {
//        String now = DateUtil.now();
//        String sql = "insert into blog (uid, content, images, time, state) VALUES (?, ?, ?, ?, 1);";
//        int result=0;
//        try{
//            result = this.executeUpdate(sql, blog.getuId(),blog.getContent(),blog.getImages(),now);
//        }catch (Exception e) {
//            e.printStackTrace();
//            throw e;
//        }
//        return result;
//    }
//
//    @Override
//    public int getPopBlogsCount(int uId, String keyword) throws SQLException {
//        ResultSet rs = null;
//        //获取微博总数
//        String sql = "select count(*) from blog b1 left join blog b2 on b1.fromBid=b2.id left join user u on b1.uid = u.id " +
//                "where (u.ispublic = 'T' or (u.ispublic = 'F' and u.id in (select followedUid from follow where uid = ?))) and (b1.content like ? or b2.content like ? ) ";
//        int count = 0;
//        try{
//            rs = this.executeQuery(sql, uId, "%"+keyword+"%", "%"+keyword+"%");
//            while (rs.next()) {
//                count = rs.getInt("count(*)");
//            }
//        }catch (SQLException e) {
//            e.printStackTrace();
//            throw e;
//        } finally {
//            DatabaseUtil.closeAll(null, null, rs);
//        }
//        return count;
//    }
//
//    @Override
//    public List<Blog> getPopBlogsByPage(int uId, String keyword, int pageNo, int pageSize) throws SQLException {
//        List<Blog> list = new ArrayList<Blog>();
//        ResultSet rs = null;
//        //分页获取一周热门微博列表
//        String fuzzyQuery = ""; //定义模糊查询部分字符串
//        if(keyword!=null && !keyword.equals("")){
//            fuzzyQuery = "and (b1.content like '%" + keyword + "%' or b3.content like '%" + keyword + "%' ) " ;
//        }
//
//        //统计一周内的热度排名
//        String sql = "select b1.id, b1.uid, b1.content, b1.images, b1.time, b1.state, u1.nickname as u_nickname, u1.image as u_image, b3.id as f_id, b3.content as f_content, u2.nickname as f_nickname, b3.images as f_images, b1.fromBid, (count(distinct(c.id)) + count(distinct(l.id)) + count(distinct(b2.id))) as pop_count " +
//                "from blog b1 left join comment as c on b1.id = c.bid and DATEDIFF(now(),c.time)<=7 " +
//                "left join likes  as l on b1.id = l.bid and DATEDIFF(now(), l.time)<=7 " +
//                "left join blog as b2 on b2.fromBid = b1.id and DATEDIFF(now(),b2.time)<=7 " +
//                "left join user as u1 on b1.uid = u1.id " +
//                "left join blog as b3 on b3.id = b1.fromBid " +
//                "left join user as u2 on b3.uid = u2.id " +
//                "where (u1.ispublic = 'T' or (u1.ispublic = 'F' and b1.uid in (select followedUid from follow where uid = ? ))) " +
//                fuzzyQuery +
//                "group by b1.id  " +
//                "order by pop_count desc LIMIT ?, ?";
//        try{
//            rs = this.executeQuery(sql,uId, (pageNo - 1) * pageSize,pageSize);
//            Blog blog = null;
//            while (rs.next()) {
//                blog = new Blog();
//                blog.setId(rs.getInt("id"));
//                blog.setuId(rs.getInt("uid"));
//                blog.getUser().setNickname(rs.getString("u_nickname"));
//                blog.getUser().setImage(rs.getString("u_image"));
//                blog.setContent(rs.getString("content"));
//                blog.setImages(rs.getString("images"));
//                blog.setTime(rs.getTimestamp("time"));
//                blog.setState(rs.getInt("state"));
//
//                if(blog.getState()==2){
//                    Blog fBlog = new Blog();
//                    //检查转发的原始微博是否被删除
//                    if(rs.getObject("f_id")!=null){
//                        fBlog.setId(rs.getInt("f_id"));
//                        fBlog.getUser().setNickname(rs.getString("f_nickname"));
//                        fBlog.setContent(rs.getString("f_content"));
//                        fBlog.setImages(rs.getString("f_images"));
//                        blog.setOrigin(fBlog);
//                    }
//                }
//                list.add(blog);
//            }
//        }catch (SQLException e) {
//            e.printStackTrace();
//            throw e;
//        } finally {
//            DatabaseUtil.closeAll(null, null, rs);
//        }
//        return list;
//    }
//
//    @Override
//    public int getBlogCountByUidAndKeyword(int uId, String keyword) throws SQLException {
//        ResultSet rs = null;
//        //获取我的微博数
//        String sql = "select count(*) from blog b1 left join blog b2 on b1.fromBid=b2.id " +
//                "where b1.uid = ? and b1.content like ? or b2.content like ? ";
//        int count = 0;
//
//        try{
//            rs = this.executeQuery(sql, uId, "%"+keyword+"%", "%"+keyword+"%");
//            while (rs.next()) {
//                count = rs.getInt("count(*)");
//            }
//        }catch (SQLException e) {
//            e.printStackTrace();
//            throw e;
//        } finally {
//            DatabaseUtil.closeAll(null, null, rs);
//        }
//        return count;
//    }
//
//    @Override
//    public List<Blog> getMyBlogsByPage(int uId, String keyword, int pageNo, int pageSize) throws SQLException {
//        List<Blog> list = new ArrayList<Blog>();
//        ResultSet rs = null;
//        //分页获取我的所有微博,并按照降序排列
//        String sql = "select b1.id,b1.uid,b1.content,b1.images,b1.time,b1.state,b2.id as f_id, b2.content as f_content, " +
//                "b2.images as f_images, user.nickname as f_nickname from blog b1 left join blog b2  on b1.fromBid = b2.id " +
//                "left join user on b2.uid = user.id where b1.uid = ? and (b1.content like ? or b2.content like ?) order by b1.time desc LIMIT ?, ? ";
//
//        try{
//            rs = this.executeQuery(sql,uId,"%"+keyword+"%","%"+keyword+"%",(pageNo - 1) * pageSize,pageSize);
//            Blog blog = null;
//            while (rs.next()) {
//                blog = new Blog();
//                blog.setId(rs.getInt("id"));
//                blog.setuId(rs.getInt("uid"));
//                blog.setContent(rs.getString("content"));
//                blog.setImages(rs.getString("images"));
//                blog.setTime(rs.getTimestamp("time"));
//                blog.setState(rs.getInt("state"));
//                if(blog.getState()==2){
//                    Blog fBlog = new Blog();
//                    //检查转发的原始微博是否被删除
//                    if(rs.getObject("f_id")!=null){
//                        fBlog.setId(rs.getInt("f_id"));
//                        fBlog.getUser().setNickname(rs.getString("f_nickname"));
//                        fBlog.setContent(rs.getString("f_content"));
//                        fBlog.setImages(rs.getString("f_images"));
//                        blog.setOrigin(fBlog);
//                    }
//                }
//                list.add(blog);
//            }
//        }catch (SQLException e) {
//            e.printStackTrace();
//            throw e;
//        } finally {
//            DatabaseUtil.closeAll(null, null, rs);
//        }
//        return list;
//    }
//
//    @Override
//    public int forwardBlog(int uId, String content, int fromBid) throws SQLException {
//        String now = DateUtil.now();
//        String sql = "insert into blog (uid, content, time, state, fromBid) VALUES (?, ?, ?, ?, ?);";
//        int result=0;
//        try{
//            result = this.executeUpdate(sql, uId,content,now,2, fromBid);
//        }catch (Exception e) {
//            e.printStackTrace();
//            throw e;
//        }
//        return result;
//    }
//}
